import csv


def csv2dict(path):
    f = open(path)
    reader = csv.reader(f)
    h = reader.next()
    results = []
    for l in reader:
        r = dict(zip(h, l))
        results.append(r)
    return results


def extract_data(src, tgt, schema, target_flds):
    f = open(src)
    o = open(tgt, 'w')
    writer = csv.writer(o, lineterminator='\n')
    writer.writerow(target_flds)
    reader = csv.reader(f)
    h = reader.next()
    flds = csv2dict(schema)
    id_list = []
    for l in reader:
        r = dict(zip(h, l))
        row = {}
        for fld in flds:
            old_fld = fld['old_fld']
            new_fld = fld['new_fld']
            if old_fld and new_fld in target_flds:
                v = r[old_fld]
                row[new_fld] = v
        if 'moisture_content' in target_flds:
        #compaction_test table
            row['PARENT_RECORD_ID'] =  '%s-%s' % (r['DLZJobNo'], r['DATE'])
            row['specified'] =  row['specified'].replace('%', '')
            row['ID'] = 'DLZ%s' % row['ID']
            try:
                if float(row['moisture_constentlab'])>0:
                    row['moisture_content'] = row['moisture_contentlab']
                    row['dry_unit_weight'] = row['dry_unit_weight_lab']
                
                    
            except:
                row['moisture_content'] = row['moisture_contentfield']
                row['dry_unit_weight'] = row['dry_unit_weight_field']
        else:
            row['ID'] = '%s-%s' % (r['DLZJobNo'], r['DATE'])
            row['project_key'] = row['project_number']
        row2 = []
        
        for fld in target_flds:
            row2.append(row.get(fld))
        if row['ID'] in id_list:
            pass
        else:
            writer.writerow(row2)
        id_list.append(row['ID'])
    f.close()
    o.close()
            
        
 
COMPACTION_FORM_FLDS = 'ID	PARENT_RECORD_ID	PARENT_PAGE_ID	PARENT_ELEMENT_ID	CREATED_DATE	CREATED_BY	CREATED_LOCATION	CREATED_DEVICE_ID	MODIFIED_BY	MODIFIED_DATE	MODIFIED_LOCATION	MODIFIED_DEVICE_ID	project_key	form_date	nuclear_no	model	ds1	ds2	ms	tested_by	compaction	test_comment	client_name	project_number	project_name'.split()
COMPACTION_TEST_FLDS = 'ID	PARENT_RECORD_ID	PARENT_PAGE_ID	PARENT_ELEMENT_ID	CREATED_DATE	CREATED_BY	CREATED_LOCATION	CREATED_DEVICE_ID	MODIFIED_BY	MODIFIED_DATE	MODIFIED_LOCATION	MODIFIED_DEVICE_ID	test_no	report_test_no	test_location	test_interval	wet_unit_weight	moisture_contentfield	dry_unit_weight_field	one_point_data	soil_and_mold	mold	factor	soil_unit_weight	one_point_moisture	moisture_remarks	enter_lab_mositure_data	moisture_contentlab	dry_unit_weight_lab	dry_unit_weight	moisture_content	curve_no	max_dry_unit_weight	optimum_moisture_content	specified	obtained	remarks	data_rivewed	check_curves	voice_note	photo	test_comment	lat	lon	dlz_status'.split()

schema = 'old_db_schema.csv'
src = 'compaction.csv'
tgt = 'compaction_form.csv'
target_flds = COMPACTION_FORM_FLDS
extract_data(src, tgt, schema, target_flds)

schema = 'old_db_schema.csv'
src = 'compaction.csv'
tgt = 'compaction_test.csv'
target_flds = COMPACTION_TEST_FLDS
extract_data(src, tgt, schema, target_flds)
